// 初始化表数据
const sqlite3 = require('sqlite3').verbose()
const { app, BrowserWindow } = require('electron')
const path = require('path')

class SqliteManage {
  constructor() {
    // 数据库连接对象
    this.db = new sqlite3.Database(path.join(app.getPath('userData'), 'database.db'), (err) => {
        if (err) {
            console.error('Could not connect to database', err)
        } else {
            console.log('Connected to database')
        }
    })
    this.init()
  }
  // 初始化数据库连接
  init() {
    // 创建表
    this.db.serialize(() => {
      console.log('sqlite start init!')
      // 创建聊天房间表
      this.db.run(`CREATE TABLE IF NOT EXISTS chat_rooms (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                roomId TEXT,
                top INTEGER,
                name TEXT,
                type INTEGER,
                avatar TEXT,
                noMessage INTEGER,
                lastMessage TEXT,
                username TEXT
            )`)
      // 聊天记录
      this.db.run(`CREATE TABLE IF NOT EXISTS chat_records (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                messageId TEXT,
                roomId TEXT,
                sendName TEXT,
                sendUserId TEXT,
                avatar INTEGER,
                refMessage INTEGER,
                status INTEGER,
                type INTEGER,
                message TEXT,
                img TEXT,
                video TEXT,
                volice TEXT,
                time TEXT
            )`)
      // 用户记录
      this.db.run(`CREATE TABLE IF NOT EXISTS local_user (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            userId TEXT,
            createTime TEXT
            )`)
      // 好友关系列表      
      this.db.run(`CREATE TABLE IF NOT EXISTS friends_relation (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            relationId TEXT,
            masterId TEXT,
            userId TEXT,
            avatar TEXT,
            loginName TEXT,
            username TEXT,
            status INTEGER
            )`)
      // 好友关系列表      
      this.db.run(`CREATE TABLE IF NOT EXISTS friends_requests (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            requestId TEXT,
            requesterId TEXT,
            receiverId TEXT,
            createTime TEXT,
            status INTEGER,
            requestMessage TEXT,
            requestUserName TEXT,
            requestAvatar TEXT
            )`)      
      console.log('sqlite init success!')
    })
  }

  // 查询用户是否登录过
  queryUserIsLogin = async (params) => {
    try {
      const rows = await new Promise((resolve, reject) => {
        // 查询总数量
        this.db.get(
          `select count(0) as count from local_user where userId = ?`,
          [params.username],
          (err, result) => {
            if (err) {
              reject({ success: false, error: err.error })
            } else {
              resolve({ success: true, data: { loginCount: result.count } })
            }
          }
        )
      })
      return rows
    } catch (err) {
      return { success: false, error: '查询用户同步出现了错误' }
    }
  }

  // 修改房间未读数量
  updateRoomsNoMessage = async (params) => {
    const sql = params.reset ? `update chat_rooms set noMessage = 0 where roomId = ? ` : `update chat_rooms set noMessage = noMessage+1 where roomId = ? `
    console.log(`@SQL ${sql} ${params.roomId}`, )
    try {
      const rows = await new Promise((resolve, reject) => {
        // 查询总数量
        this.db.all(sql, [params.roomId],
          (err, result) => {
            if (err) {
              reject({ success: false, error: err.error })
            } else {
              resolve({ success: true})
            }
          }
        )
      })
      return rows
    } catch (err) {
      return { success: false, error: '查询用户同步出现了错误' }
    }
  }

  // 查询用户是否登录过
  updateUserIsLogin = async (params) => {
    try {
      const rows = await new Promise((resolve, reject) => {
        // 已经不存在了， 就插入一条数据
        this.db.all(
          `insert into local_user (userId, createTime) values (?, datetime('now'))`,
          [params.username],
          (err, rows) => {
            if (err) {
              reject({ success: false, error: err.error })
            } else {
              resolve({ success: true, result: rows })
            }
          }
        )
      })
      return rows
    } catch (err) {
      return { success: false, error: '查询用户同步出现了错误' }
    }
  }



  // ********************************************************聊天房间******************************************************************** //
  // 批量插入聊天房间
  batchInsertRooms(roomList, userId) {
    // 开始事务
    this.db.run('BEGIN TRANSACTION', () => {
      const requests = roomList.map((item) => {
        return new Promise((resolve, reject) => {
          this.db.run(
            'INSERT INTO chat_rooms (top, name, type, avatar, noMessage, lastMessage, username, roomId) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
            [
              item.top,
              item.name,
              item.type,
              item.avatar,
              item.noMessage,
              item.lastMessage,
              userId,
              item.id
            ],
            (err) => {
              if (err) {
                reject('Error inserting data:', err)
              } else {
                resolve()
              }
            }
          )
        })
      })
      Promise.all(requests).then(() => {
        // 提交事务
        this.db.run('COMMIT', (err) => {
          if (err) {
            console.error('Error committing transaction:', err)
          } else {
            console.log('Data inserted successfully')
          }
        })
      })
    })
  }
  // 查询聊天房间
  queryChatRooms = async (params) => {
    try {
      const rows = await new Promise((resolve, reject) => {
        this.db.all(
          `SELECT cr.noMessage, cr.id, cr.roomId, cr.name, cr.type, cr.avatar, cr.noMessage, cr.username, 
                    (
                        SELECT json_object(
                            'content', CASE
                                WHEN message IS NOT NULL AND message <> '' THEN message
                                WHEN img IS NOT NULL AND img <> '' THEN '[图片]'
                                WHEN volice IS NOT NULL AND volice <> '' THEN '[语音]'
                                WHEN video IS NOT NULL AND video <> '' THEN '[视频]'
                                ELSE ''
                            END,
                            'time', time
                        )
                        FROM chat_records
                        WHERE roomId = cr.roomId and type <> -1
                        ORDER BY id DESC
                        LIMIT 1
                    ) AS lastMessage
            FROM chat_rooms cr
            WHERE cr.username = ?`,
          [params.username],
          (err, rows) => {
            if (err) {
              reject({ success: false, error: err.error })
            } else {
              resolve({ success: true, list: rows })
            }
          }
        )
      })
      return { success: true, data: rows }
    } catch (err) {
      console.log('@err', err)
      return { success: false, error: '查询出现了错误' }
    }
  }
  // ********************************************************聊天房间******************************************************************** //



  // ********************************************************好友列表******************************************************************** //
  // 批量插入好友
  batchInsertFriends(params) {
    // 开始事务
    this.db.run('BEGIN TRANSACTION', () => {
      const requests = params.relationList.map((item) => {
        return new Promise((resolve, reject) => {
          this.db.run(
            'INSERT INTO friends_relation (relationId, masterId, userId, avatar, loginName, username, status) VALUES (?, ?, ?, ?, ?, ?, ?)',
            [item.relationId, item.masterId, item.userId, item.avatar, item.loginName, item.userName, item.status],
            (err) => {
              if (err) {
                reject('Error inserting data:', err)
              } else {
                resolve()
              }
            }
          )
        })
      })
      Promise.all(requests).then(() => {
        // 提交事务
        this.db.run('COMMIT', (err) => {
          if (err) {
            console.error('Error committing transaction:', err)
          } else {
            console.log('FriendsRelation Data inserted successfully')
          }
        })
      })
    })
  }
  // 查询好友列表
  queryFriendRelation = async (params) => {
    try {
        const rows = await new Promise((resolve, reject) => {
          this.db.all(
            `select * from friends_relation where masterId = ?`,
            [params.username],
            (err, rows) => {
              if (err) {
                reject({ success: false, error: err.error })
              } else {
                resolve({ success: true, list: rows })
              }
            }
          )
        })
        return { success: true, data: rows }
      } catch (err) {
        console.log('@err', err)
        return { success: false, error: '查询出现了错误' }
      }
  }
  // ********************************************************好友列表******************************************************************** //



  // ********************************************************好友请求******************************************************************** //
  // 查询好友请求列表
  queryFriendRequests = async (params) => {
    try {
        const rows = await new Promise((resolve, reject) => {
          this.db.all(
            `select * from friends_requests where receiverId = ?`,
            [params.userId],
            (err, rows) => {
              if (err) {
                reject({ success: false, error: err.error })
              } else {
                resolve({ success: true, list: rows })
              }
            }
          )
        })
        return { success: true, data: rows }
      } catch (err) {
        console.log('@err', err)
        return { success: false, error: '查询出现了错误' }
      }
  }
  // 批量插入请求
  batchInsertRequests(params) {
    // 开始事务
    this.db.run('BEGIN TRANSACTION', () => {
      const requests = params.requestList.map((item) => {
        return new Promise((resolve, reject) => {
          this.db.run(
            'INSERT INTO friends_requests (requestId, requesterId, receiverId, createTime, status, requestMessage, requestUserName, requestAvatar) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
            [item.id, item.requesterId, item.receiverId, item.createTime, item.status, item.requestMessage, item.requestUserName, item.requestAvatar],
            (err) => {
              if (err) {
                reject('Error inserting data:', err)
              } else {
                resolve()
              }
            }
          )
        })
      })
      Promise.all(requests).then(() => {
        // 提交事务
        this.db.run('COMMIT', (err) => {
          if (err) {
            console.error('Error committing transaction:', err)
          } else {
            console.log('FriendsRelation Data inserted successfully')
          }
        })
      })
    })
  }
  insertRequest(request) {
    return new Promise((resolve, reject) => {
      this.db.run(
        'INSERT INTO friends_requests (requestId, requesterId, receiverId, createTime, status, requestMessage, requestUserName, requestAvatar) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
        [request.id, request.requesterId, request.receiverId, request.createTime, request.status, request.requestMessage, request.requestUserName, request.requestAvatar
        ],
        function (err) {
          if (err) {
            reject('Error inserting data:', err)
          } else {
            resolve(this.lastID)
          }
        }
      )
    })
  }
  // ********************************************************好友请求******************************************************************** //



  // ********************************************************聊天记录******************************************************************** //
  // 批量插入聊天记录
  batchInsertChatRecords = (recordsList) => {
    // 开始事务
    this.db.run('BEGIN TRANSACTION', () => {
      const requests = recordsList.map((item) => {
        return new Promise((resolve, reject) => {
          this.db.run(
            'INSERT INTO chat_records (messageId, roomId, sendUserId, sendName, avatar, message, refMessage, status, type, img, video, volice, time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
            [
              item.id,
              item.roomId,
              item.sendUserId,
              item.sendUserName,
              item.avatar,
              item.message,
              item.refMessage,
              1,
              item.type,
              item.img,
              item.video,
              item.volice,
              item.createTime
            ],
            (err) => {
              if (err) {
                reject('Error inserting data:', err)
              } else {
                resolve()
              }
            }
          )
        })
      })
      Promise.all(requests).then(() => {
        // 提交事务
        this.db.run('COMMIT', (err) => {
          if (err) {
            console.error('Error committing transaction:', err)
          } else {
            console.log('Data inserted successfully!')
          }
        })
      })
    })
  }
  // 单次插入聊天记录
  insertChatRecord = (record) => {
    return new Promise((resolve, reject) => {
      const result = this.existMessage(record.id)
      if(result==0 || record.messageId == 0) {
        this.db.run(
          'INSERT INTO chat_records (messageId, roomId, sendName, sendUserId, avatar, message, refMessage, status, type, img, video, volice, time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
          [
            record.id,
            record.roomId,
            record.sendUserName,
            record.sendUserId,
            record.avatar,
            record.message,
            record.refMessage,
            record.status,
            record.type,
            record.img,
            record.video,
            record.volice,
            record.time
          ],
          function (err) {
            if (err) {
              reject('Error inserting data:', err)
            } else {
              resolve(this.lastID)
            }
          }
        )
      } else {
        resolve(0)
      }
    })
  }
  // 根据消息ID判断是否有当前这条消息
  existMessage = (messageId) => {
    return new Promise((resolve1, reject1) => {
      this.db.get(
        `select count(0) from chat_records where messageId = ?`,
        [messageId],
        (err, result) => {
          if (err) {
            reject1()
          } else {
            resolve1(result.count)
          }
        }
      )
    })
  }
  // 根据RoomId查询聊天记录
  queryChatRecords = async (params) => {
    try {
      const rows = await new Promise((resolve, reject) => {
        // 查询总数量
        this.db.get(
          `select count(0) as count from chat_records where roomId = ?`,
          [params.roomId],
          (err, result) => {
            if (err) {
              reject({ success: false, error: err.error })
            } else {
              // 如果当前查询的数量已经超过总数量
              if (params.start > result.count) {
                resolve({ success: true, data: { next: false, list: [] } })
              } else {
                // 查询聊天记录
                this.db.all(
                  `select * from (select * from chat_records where roomId = ? order by id desc) limit ?, ?`,
                  [params.roomId, params.start, params.pos],
                  (err, rows) => {
                    if (err) {
                      reject({ success: false, error: err.error })
                    } else {
                      resolve({
                        success: true,
                        data: { next: result.count > params.start + params.pos, list: rows }
                      })
                    }
                  }
                )
              }
            }
          }
        )
      })
      return rows
    } catch (err) {
      return { success: false, error: '查询出现了错误' }
    }
  }
  // 根据RoomId查询聊天记录
  queryChatTheLatestRecords = async (params) => {
    try {
      const rows = await new Promise((resolve, reject) => {
        this.db.all(
          `SELECT
                time,
                CASE
                    WHEN message IS NOT NULL AND message <> '' THEN message
                    WHEN img IS NOT NULL AND img <> '' THEN '[图片]'
                    WHEN volice IS NOT NULL AND volice <> '' THEN '[语音]'
                    WHEN video IS NOT NULL AND video <> '' THEN '[视频]'
                    ELSE ''
                END as content
                FROM
                chat_records 
                WHERE
                roomId = ?
                and type != -1	
                order by time desc
                limit 0, 1`,
          [params.roomId],
          (err, rows) => {
            if (err) {
              reject({ success: false, error: err.error })
            } else {
              resolve({ success: true, data: { list: rows } })
            }
          }
        )
      })
      return rows
    } catch (err) {
      return { success: false, error: '查询出现了错误' }
    }
  }
  // 修改聊天记录的状态
  updateChatRecordStatus = async (params) => {
    try {
      const rows = await new Promise((resolve, reject) => {
        this.db.all(
          `update chat_records set status = ?, messageId = ? where id = ?`,
          [params.status, params.messageId, params.id],
          (err, rows) => {
            if (err) {
              reject({ success: false, error: err.error })
            } else {
              resolve({ success: true, result: rows })
            }
          }
        )
      })
      return { success: true, data: rows }
    } catch (err) {
      console.log('@err', err)
      return { success: false, error: '查询出现了错误' }
    }
  }
  // ********************************************************聊天记录******************************************************************** //
}

module.exports = SqliteManage
